package sc.dao;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

import sc.vo.Student;

public class StudentDAO implements InterfaceDAO<Student>{
	private Connection con = loadDB();
	private Statement stmt = null;
	private ResultSet rs = null;
	private ArrayList<Student> lists = list();
	
	@Override
	public ArrayList<Student> list() {
		lists = new ArrayList<Student>();
		String item_sql = "select stdNo, stdName, kor, eng, math from student";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(item_sql);
			while(rs.next()) {
				Student std = new Student();
				std.setStdNo(rs.getInt("stdNo"));
				std.setStdName(rs.getString("stdName"));
				std.setKor(rs.getInt("kor"));
				std.setEng(rs.getInt("eng"));
				std.setMath(rs.getInt("math"));
				lists.add(std);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return lists;
	}
	
	@Override
	public void insertItem(Student item) {
		String sql = "";
		try {
			stmt = con.createStatement();
			if(!lists.contains(item)) {
				lists.add(item);
				sql = "insert into student(stdNo ,stdName, kor, eng, math) values("+ "'" + item.getStdNo() + "'" + "," + "'" + item.getStdName() + "'" + "," + "'" + item.getKor() + "'" + "," + "'" + item.getEng() + "'" + ", " + "'"+ item.getMath() + "'"+ ")";
				int in = stmt.executeUpdate(sql);
				if(in == 1) 
					System.out.println("저장 완료!");
				else
					System.out.println("저장 실패");
			} else {
				System.out.println("이미 존재하는 학생입니다.");
			} 
		} catch (SQLException e) {
			e.printStackTrace();
		} 
	}

	@Override
	public void updateItem(Student item) {
		String sql = "";
		try {
			stmt = con.createStatement();
			if(lists.contains(item)) {
				lists.set(lists.indexOf(item), item);
				sql = "update student set stdNo = " + "'" + item.getStdNo() + "'" + ", stdName = " + "'" + item.getStdName() + "'" + ", kor = " + "'" + item.getKor() + "'" + ", eng = " + "'" + item.getEng() + "'" + ", math = " + "'" + item.getMath() + "'" + "where stdNo = "  + "'" + item.getStdNo() + "'" + "and stdName = " + "'" + item.getStdName() + "'";
				int update = stmt.executeUpdate(sql);
				if(update == 1) 
					System.out.println("업데이트 완료");
				else
					System.out.println("업데이트 실패");
			} else {
				System.out.println("존재하지 않는 학생입니다.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	@Override
	public void deleteItem(Student item) {
		String sql = "";
		try {
			stmt = con.createStatement();
			if(lists.contains(item)) {
				lists.remove(item);
				sql = "delete from student where stdNo = " + "'" + item.getStdNo() + "'" + "and stdName = " + "'" + item.getStdName() + "'";
				int del = stmt.executeUpdate(sql);
			} else {
				System.out.println("존재하지 않는 학생입니다.");
			}
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public Student searchItem(Student item) {
		return lists.get(lists.indexOf(item));
	}

	@Override
	public void prnListItem(Comparator<Student> comp) {
		if(comp == null) {
			Collections.sort(lists);
		} else {
			Collections.sort(lists, comp);
		}
		
		for(Student std : lists) {
			System.out.println(std);
		}
	}

	@Override
	public Connection loadDB() {
		Connection con = null;
		String url = "jdbc:mysql://localhost/sc_db";
		String id = "root";
		String pw = "1111";
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
//			System.out.println("드라이버 연결 성공!");
			con = DriverManager.getConnection(url, id, pw);
//			System.out.println("데이터베이스 연결 성공!");
		} catch (ClassNotFoundException e) {
			System.out.println("JDBC 드라이버 찾을 수 없습니다.");
		} catch (SQLException e) {
			System.out.println("데이터베이스에 연결할 수가 없습니다.");
		}
		return con;
	}

	@Override
	public void closeDB() {
		if(rs != null) try{rs.close();}catch(Exception e) {}
		if(stmt != null) try{stmt.close();} catch(Exception e) {}
		if(con != null) try{con.close();} catch(Exception e) {}
	}
}